CREATE TABLE [dbo].[Content_File]
(
[ContentFileID] [numeric] (18, 0) NOT NULL IDENTITY(1, 1),
[ContentID] [numeric] (18, 0) NOT NULL,
[FileTypeCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SourceLocation] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PublishLocation] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FileName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SortOrder] [numeric] (18, 0) NULL,
[OriginalContentFileID] [numeric] (18, 0) NULL,
[PreviousContentFileID] [numeric] (18, 0) NULL
) ON [PRIMARY]

GO
-- =============================================
-- This trigger sets OriginalContentFileID to the ID of the content file inserted.
-- =============================================
CREATE TRIGGER [dbo].[asi_Content_File_Insert]
    ON [dbo].[Content_File]
    FOR INSERT
AS
BEGIN
    UPDATE Content_File SET OriginalContentFileID = ContentFileID
    WHERE ContentFileID IN (SELECT ContentFileID FROM inserted
                            WHERE OriginalContentFileID IS NULL)
END

GO
ALTER TABLE [dbo].[Content_File] ADD CONSTRAINT [PK_Content_File] PRIMARY KEY CLUSTERED ([ContentFileID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ix_Content_File] ON [dbo].[Content_File] ([ContentID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Content_File_FileTypeCode] ON [dbo].[Content_File] ([FileTypeCode]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ix_ContentFile_OriginalContentFileID] ON [dbo].[Content_File] ([OriginalContentFileID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ix_ContentFile_PreviousContentFileID] ON [dbo].[Content_File] ([PreviousContentFileID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Content_File] ADD CONSTRAINT [FK_Content_File_Content] FOREIGN KEY ([ContentID]) REFERENCES [dbo].[Content] ([ContentID])
GO
ALTER TABLE [dbo].[Content_File] ADD CONSTRAINT [FK_Content_File_OriginalContentFileID] FOREIGN KEY ([OriginalContentFileID]) REFERENCES [dbo].[Content_File] ([ContentFileID])
GO
ALTER TABLE [dbo].[Content_File] ADD CONSTRAINT [FK_Content_File_PreviousContentFileID] FOREIGN KEY ([PreviousContentFileID]) REFERENCES [dbo].[Content_File] ([ContentFileID])
GO
ALTER TABLE [dbo].[Content_File] ADD CONSTRAINT [FK_ContentFile_FileType] FOREIGN KEY ([FileTypeCode]) REFERENCES [dbo].[File_Type_Ref] ([FileTypeCode])
GO
GRANT REFERENCES ON  [dbo].[Content_File] TO [IMIS]
GRANT SELECT ON  [dbo].[Content_File] TO [IMIS]
GRANT INSERT ON  [dbo].[Content_File] TO [IMIS]
GRANT DELETE ON  [dbo].[Content_File] TO [IMIS]
GRANT UPDATE ON  [dbo].[Content_File] TO [IMIS]
GO